package com.example.myproject.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface AnalyseMapper {

    @Select("SELECT COALESCE(SUM(download), 0) FROM user_ratio")
    Long getTotalDownload();

    @Select("""
        SELECT DATE_FORMAT(time, '%Y-%m') AS month,
               SUM(upload) AS upload_sum,
               SUM(download) AS download_sum
          FROM user_ratio_log
         WHERE user_id = #{userId}
           AND time >= DATE_FORMAT(CURDATE() - INTERVAL 4 MONTH, '%Y-%m-01')
         GROUP BY month
         ORDER BY month DESC
         LIMIT 4
    """)
    List<Map<String, Object>> getUserMonthlyStats(@Param("userId") Long userId);

    @Select("""
        SELECT DATE_FORMAT(time, '%Y-%m') AS month,
               SUM(upload) AS upload_sum,
               SUM(download) AS download_sum
          FROM user_ratio_log
         WHERE time >= DATE_FORMAT(CURDATE() - INTERVAL 4 MONTH, '%Y-%m-01')
         GROUP BY month
         ORDER BY month DESC
         LIMIT 4
    """)
    List<Map<String, Object>> getAllMonthlyStats();

    @Select("SELECT COUNT(*) FROM torrent")
    Long getTotalTorrentCount();

    @Select("SELECT COUNT(*) FROM user WHERE user_status = 'active'")
    Long getActiveUserCount();
}
